package zuoye.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import zuoye.DBUtil;

public class StudentDao {
	public static final StudentDao DAO = new StudentDao();
	public static final int PAGESIZE = 4;// 页大小

	public int add(Student s) {
		String sql = "insert into student (fromcity,wechat,address,sname,sex,telphone,gradeid,birthday,pass,qq) values (?,?,?,?,?,?,?,str_to_date(?,'%Y-%m-%d'),?,?)";
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		try {
			int index = 1;
			st = con.prepareStatement(sql);
			st.setString(index++, s.getFromcity());
			st.setString(index++, s.getWechat());
			st.setString(index++, s.getAddr());
			st.setString(index++, s.getSname());
			st.setString(index++, s.getSex());
			st.setString(index++, s.getTelephone());
			st.setInt(index++, s.getGradeid());
			st.setObject(index++, s.getBirthday());
			st.setString(index++, s.getPass());
			st.setString(index++, s.getQq());
			int rs = st.executeUpdate();
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(st);
			DBUtil.close(con);
		}

		return 0;
	}

	// 根据学号删除
	public int del(int stuno) {
		String str = "delete from student where stuno=?";
		int k = DBUtil.executeUpdate(str, stuno);
		return k;
	}

	public int update(Student s) {
		String sql = "update student set sname=";
		return 0;
	}

	// 根据学号读取学生对象
	public Student get(int stuno) {
		String sql = "select * from student where stuno=?";
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, stuno);
			rs = ps.executeQuery();
			if (rs.next()) {
				Student s = new Student();
				s.setStuno(stuno);
				s.setSname(rs.getString("sname"));
				s.setTelephone(rs.getString("telphone"));
				s.setWechat(rs.getString("wechat"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getDate("birthday"));
				// ...还有省略
				return s;
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(rs, ps, con);
		}
		return null;
	}

	/**
	 * 根据页号返回分页的数据 .即使找不到，返回一个空集合，不返回null
	 * 
	 * @param pageNo
	 * @return
	 */
	public List<Student> search(int pageNo) {
		String sql = "select * from student limit ?,?";
		int start = (pageNo - 1) * PAGESIZE;
		List<Student> datas = new java.util.ArrayList<Student>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, PAGESIZE);
			rs = ps.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setTelephone(rs.getString("telphone"));
				s.setWechat(rs.getString("wechat"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getDate("birthday"));
				// ...还有省略
				datas.add(s);// !!!!!!!!!!!!!!!
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(rs, ps, con);
		}
		return datas;
	}
}
